* D:\E\replications\BJPS2020\resultsets\resultset_drops.do
	// was called variability_december2017.do

set more off

* cd D:\E\replications\BJPS2020\
clear all
cap log close
log using ./resultsets/resultset_drops.txt, text replace
display "$S_TIME  $S_DATE"
about
ado dir

	
******
* ECHP
******
use ./rawdata/echp/ESIs_ages25-60_echp.dta, clear
append using ./rawdata/echp/ESIs_echp.dta

foreach v in mi_e di_e hi100_e {
	if "`v'"=="hi100_e"   local foo "Total net HH income (ECHP)"
	else if "`v'"=="mi_e" local foo "Market HH income"
	else if "`v'"=="di_e" local foo "Disposable HH income"
	label var gini_`v'_2560 "Gini of `foo', ages 25-60"
	label var gini_`v'_all "Gini of `foo', ages all"
	foreach j in 00 10 25 50 {
		label var ESI`j'_`v' "`j'+% arc drop, `foo'"
		label var upESI`j'_`v' "`j'+% arc gain, `foo'"
		label var gESI`j'_`v' "`j'+% drop, `foo'"
		label var upgESI`j'_`v' "`j'+% gain, `foo'"
	}
}
cap drop gini_mi_e gini_di_e gini_hi100_e
isid ages ccode year
foreach v in mi di {
	gen gini_`v'_e=gini_`v'_e_all if ages=="all"
	replace gini_`v'_e=gini_`v'_e_2560 if ages=="25-60"
}
label var gini_mi_e "Gini of HH-level market income"
label var gini_di_e "Gini of HH-level disposable income"
label var my_mi_e "Mean of HH-level market income"
label var my_di_e "Mean of HH-level disposable income"
label var sd_mi_e "SD of HH-level market income"
label var sd_di_e "SD of HH-level disposable income"

drop gini_mi_e_2560 gini_mi_e_all gini_di_e_2560 gini_di_e_all gini_hi100_e_2560 gini_hi100_e_all

drop *hi100*

gen source="ECHP"
tempfile echp
save `echp'

***********
* EU-SILC
***********

clear
use ./rawdata/silc/ESIs_silc.dta, clear
append using ./rawdata/silc/ESIs_ages25-60_silc.dta
isid ages ccode year

drop if ccode==368 & year==2015 // clearly a data point problem

keep ccode year dataset ages *_mi_e* *_di_e* *_MI_e* *_DI_e* RR??_*

foreach j in 00 10 25 50 {
	foreach m in 1 2 3 4 {
		clonevar RR`j'_`m' =RR`j'_midi`m' 
		clonevar RR`j'_g`m'=RR`j'_gmidi`m'
	}
}

* For France
ds *_DI_*
foreach v in `r(varlist)' {
	local c: subinstr local v "_DI_" "_di_", all
	di "`v' -> `c'"
	*replace `v'=`c' if ccode==220
	replace `c'=`v' if ccode==220
}
ds *_MI_*
foreach v in `r(varlist)' {
	local c: subinstr local v "_MI_" "_mi_", all
	di "`v' -> `c'"
	*replace `v'=`c' if ccode==220
	replace `c'=`v' if ccode==220
}	
foreach j in 00 10 25 50 {
	foreach m in 1 2 3 4 {
		replace RR`j'_`m' =RR`j'_MIDI`m'  if ccode==220
		replace RR`j'_g`m'=RR`j'_gMIDI`m' if ccode==220
	}
}

drop *_MI_e* *_DI_e*  RR*MIDI* RR*midi*

foreach v in mi di {
	gen gini_`v'_e=gini_`v'_e_all if ages=="all"
	replace gini_`v'_e=gini_`v'_e_2560 if ages=="25-60"
}
label var gini_mi_e "Gini of HH-level market income"
label var gini_di_e "Gini of HH-level disposable income"
label var my_mi_e "Mean of HH-level market income"
label var my_di_e "Mean of HH-level disposable income"
label var sd_mi_e "SD of HH-level market income"
label var sd_di_e "SD of HH-level disposable income"

drop gini_mi_e_2560 gini_di_e_2560 gini_mi_e_all gini_mi_e_all gini_di_e_all

gen source="SILC"

tempfile silc
save `silc'

*******************
* CNEF *************
*******************

use ./rawdata/cnef/ESIs_cnef.dta, clear
append using ./rawdata/cnef/ESIs_ages25-60_cnef.dta
isid ages ccode year
append using ./rawdata/cnef/licensed/slid/slid_1993-2010.dta

foreach v of varlist *_nYe {
	local n: subinstr local v "_nYe" "_di_e"
	rename `v' `n'
}
foreach v of varlist *_gYe {
	local n: subinstr local v "_gYe" "_mi_e"
	rename `v' `n'
}
gen gini_di_e=gini_nYe_all if ages=="all"
replace gini_di_e=gini_nYe_2560 if ages=="25-60"
gen gini_mi_e=gini_gYe_all if ages=="all"
replace gini_mi_e=gini_gYe_2560 if ages=="25-60"
label var gini_mi_e "Gini of HH-level market income"
label var gini_di_e "Gini of HH-level disposable income"
drop gini_nYe_2560 gini_nYe_all gini_gYe_2560 gini_gYe_all
tempfile cnef
save `cnef'

*******************
* CPS *************
*******************

use ./rawdata/cps/ESIs_cps.dta, clear
append using ./rawdata/cps/ESIs_ages25-60_cps.dta
isid ages ccode year

foreach v of varlist *_nYe {
	local n: subinstr local v "_nYe" "_di_e"
	rename `v' `n'
}
foreach v of varlist *_gYe {
	local n: subinstr local v "_gYe" "_mi_e"
	rename `v' `n'
}
gen gini_di_e=gini_nYe_all if ages=="all"
replace gini_di_e=gini_nYe_2560 if ages=="25-60"
gen gini_mi_e=gini_gYe_all if ages=="all"
replace gini_mi_e=gini_gYe_2560 if ages=="25-60"
label var gini_mi_e "Gini of HH-level market income"
label var gini_di_e "Gini of HH-level disposable income"
drop gini_nYe_2560 gini_nYe_all gini_gYe_2560 gini_gYe_all

gen source="CPS"
tempfile cps
save `cps'

*********
* append
*********

clear
use `cnef', clear
append using `echp' `silc' `cps'
isid source ages dataset ccode year

* clean up

bys source ages ccode year: gen N=_N
drop if ccode==200 & !inlist(source,"BHPS","SILC")
drop if ccode==200 & source=="BHPS" & year>2006
drop if ccode==200 & source=="SILC" & year<=2006

drop if ccode==225 & source=="SILC"

replace ccode=2222 if dataset=="CNEF" & ccode==2
*drop if inlist(ccode,200,255) & dataset!="CNEF"
drop if inlist(ccode,255) & dataset!="CNEF"
label define ccode 200200 "GB (CNEF)" 2222 "USA (PSID)", modify

isid ages ccode year	

drop if inlist(ccode,2222,365)

replace year=year-1 if dataset=="SILC" & !inlist(ccode,200)
*drop if ccode==220 &year==2005

cap drop aid
gen aid=inlist(ccode,2,20,900,200,205,210,211,212,220,225,230,235,255,305,325,350,375,380,385,390,395)
cap drop iso3c
kountry ccode, from(cown) to(iso3c)
rename _ISO3C_ iso3c
replace iso3c="USA-PSID" if ccode==2222

* Risk reduction (new method)
foreach j in 10 25 50 {
	gen mi_drop`j'=RR`j'_3+RR`j'_4 // (r10+ r11)
	label var mi_drop`j' "`j'+% arc drop, HH market income"	
		* conceptually, same as ESI`j'_mi_e
	gen di_drop`j'=RR`j'_2+RR`j'_4 // (r01+ r11)
	label var di_drop`j' "`j'+% arc drop, HH disposable income"	
		* conceptually, same as ESI`j'_di_e
	*gen red1_`j'=(mi_drop`j'-di_drop`j')/mi_drop`j'
	*label var red1_`j' "Risk-reduction [(mi_drop`j'-di_drop`j')/mi_drop`j']"
	gen red2_`j'=RR`j'_3/mi_drop`j'
	label var red2_`j' "Risk-reduction [(MI-drop=1 & DI-drop=0)/MI-drops]"
}
* -> red2_`j' is our measure
* -> red1_`j' corresponds to the aggregate risk reduction measure = (ESI`j'_mi_e - ESI`j'_di_e)/ESI`j'_mi_e

* Inequality reduction
gen dgini=gini_mi_e-gini_di_e 
label var dgini "Abs. inequality reduction (gini_mi_e-gini_di_e)"
gen pgini=(gini_mi_e-gini_di_e)/gini_mi_e
label var pgini "Rel. inequality reduction (gini_mi_e-gini_di_e)/gini_mi_e"

//////////////////
// macro merges
//////////////////

* OECD SOCX
preserve
	use ./rawdata/macro/oecd_socx/socx2018.dta, clear
	keep if unit=="PCT_GDP"
	isid ccode year
	keep ccode year socx_all_10
	label var socx_all_10 "Total public social expenditure (% GDP)"
	rename socx_all_10 socx_all
	xtset ccode year
	tsfill, full
	bys ccode: ipolate socx_all year, gen(IPsocx_all)
	label var IPsocx_all "`: var label socx_all' (interpolated)"
	drop socx_all
	rename IPsocx_all socx_all
	tempfile t
	save `t'
restore
cap drop _merge
merge m:1 ccode year using `t'
drop if _merge==2
cap drop _merge


* Scruggs' generosity indices
	* Source: http://cwed2.org/
	* Downloaded Dec 3, 2019
preserve
	*use C:\Dropbox\Rehm\Data\scruggs\cwed2\cwed2genindex.dta, clear
	insheet using ./rawdata/macro/scruggs/cwed-subset_3dec2019.csv, comma clear
	keep isocode year  totgen uegen skgen pgen
	cap drop _merge
	kountry isocode, from(iso3n) to(cown)
	rename _COWN ccode
	drop isocode
	destring totgen, replace
	label var totgen "Combined Generosity Index"
	label var uegen	"Unemployment Generosity Index"
	label var skgen	"Sickness Generosity Index"
	label var pgen	"Pension Generosity Index"
	foreach v in totgen uegen skgen pgen {
		label var `v' "`: var label `v'' (Scruggs)"
		rename `v' ls_`v'
	}
	isid ccode year
	tempfile t
	save `t'
restore
cap drop _merge
merge m:1 ccode year using `t'
drop if _merge==2
cap drop _merge


cap drop aid
gen aid=inlist(ccode,2,20,900,200,205,210,211,212,220,225,230,235,255,305,325,350,375,380,385,390,395)
cap drop iso3c
kountry ccode, from(cown) to(iso3c)
rename _ISO3C_ iso3c
labmask ccode, val(iso3c)
numlabel ccode, add force

isid ages ccode year
tabstat year if ages=="25-60", by(ccode) s(min max N)

cap drop country
cap drop N

* keep varibles of interest
order source dataset ages iso3c ccode year ///
	*??_drop?? ///
	*ESI* *RR* *red2* *gini* *ls_* *socx_all*

keep source dataset ages iso3c ccode year ///
	*??_drop?? ///
	*ESI* *RR* *gini* *red2* *ls_* *socx_all*

ds source dataset ages iso3c ccode year ///
	*??_drop?? ///
	*ESI* *RR* *gini* *red2* *ls_* *socx_all* ///
	, not

isid ages ccode year
labmask ccode, val(iso3c) 
numlabel ccode, add force

label var ccode "COW country codes"

* Country averages
preserve
	keep if ESI25_mi_e<.
		// make samples comparable
	ds source dataset ages iso3c ccode year, not
	foreach v in `r(varlist)' {
		rename `v' M_`v'
	}
	ds M_*, not(type string)
	keep ccode ages `r(varlist)'
	foreach v of varlist _all {
		local l`v': var label `v'
	}
	collapse (mean) M_*, by(ccode ages)
	foreach v of varlist _all {
		label var `v' "`l`v'' (avg)"
	}
	tempfile t
	save `t'
restore
cap drop _merge
merge m:1 ccode ages using `t'
drop _merge

format *??_drop?? *ESI* *RR* *gini* *red2* %4.3f
format *ls_* *socx_all* %2.1f

compress
note: Created on `= c(current_date)'
note: ./resultsets/resultset_drops.do
note: ./resultsets/resultset_drops.dta
saveold ./resultsets/resultset_drops.dta, replace

tostring year, replace
export excel source dataset ages iso3c ccode year ESI??_* RR??_? red2_* ls_* socx_all using "./resultsets/resultset_drops_label", firstrow(varlabels) replace
export excel source dataset ages iso3c ccode year ESI??_* RR??_? red2_* ls_* socx_all using "./resultsets/resultset_drops_name", firstrow(variables) replace

display "$S_TIME  $S_DATE"
cap log close
